package com.xms.utils;

import com.xms.core.data.DataTable;
import com.xms.sdk.core.DynamicData;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 * <p>
 * ExcelUtil
 * </p>
 *
 * @author migo
 * @since 2021/10/3
 */
public class ExcelUtil {
    public static List<String> getColumns(String filePath) {
        Workbook workBook = getWorkbook(filePath);
        Sheet sheet = workBook.getSheetAt(0);
        if (null != sheet) {
            Row row = sheet.getRow(0);
            List<String> cells = new ArrayList<>();
            row.cellIterator().forEachRemaining(x -> {
                if (x.getStringCellValue() != null)
                    cells.add(x.getStringCellValue());
            });
            return cells;
        }
        return new ArrayList<>();
    }

    public static List<String> getColumns(Sheet sheet) {
        if (null != sheet) {
            Row row = sheet.getRow(0);
            List<String> cells = new ArrayList<>();
            row.cellIterator().forEachRemaining(x -> {
                if (x.getStringCellValue() != null)
                    cells.add(x.getStringCellValue());
            });
            return cells;
        }
        return new ArrayList<>();
    }

    public static List<DynamicData> toDynamicList(Sheet sheet) {
        Iterator<Row> rows = sheet.rowIterator();
        List<DynamicData> list = new ArrayList<>();
        List<String> columns = getColumns(sheet);
        rows.next();//跳过表头
        while (rows.hasNext()) {
            Row row = rows.next();
            DynamicData dr = new DynamicData();
            for (int i = 0; i < row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                dr.put(columns.get(i), getCellValue(cell));
            }
            list.add(dr);
        }

        return list;
    }

    public static List<DynamicData> toDynamicList(MultipartFile file) throws IOException {
        String fileName = file.getOriginalFilename();
        boolean isHigher = fileName.endsWith(".xlsx");
        Workbook workBook = isHigher ? new XSSFWorkbook(file.getInputStream()) :
                new HSSFWorkbook(file.getInputStream());
        Sheet sheet = workBook.getSheetAt(0);
        return toDynamicList(sheet);
    }

    public static List<DynamicData> toDynamicList(String filePath) {
        Workbook workBook = getWorkbook(filePath);
        Sheet sheet = workBook.getSheetAt(0);
        return toDynamicList(sheet);
    }

    public static DataTable toDataTable(Sheet sheet, boolean includeRownum) {
        Iterator<Row> rows = sheet.rowIterator();
        List<String> columns = getColumns(sheet);
        rows.next();//跳过表头
        List<Object[]> list = new ArrayList<>();
        int rowNum = 1;
        int cellCount = columns.size();
        while (rows.hasNext()) {
            Row row = rows.next();

            Object[] dr = includeRownum ? new Object[cellCount + 1] : new Object[cellCount];
            int start = 0;
            if (includeRownum) {
                dr[start] = rowNum;
                start++;
                rowNum++;
            }
            for (int i = 0; i < cellCount; i++) {
                Cell cell = row.getCell(i);
                dr[start + i] = getCellValue(cell);
            }
            list.add(dr);
        }
        DataTable dataTable = new DataTable(columns, list);

        return dataTable;
    }

    public static DataTable toDataTable(Sheet sheet) {
        return toDataTable(sheet, false);
    }

    public static DataTable toDataTable(MultipartFile file, boolean includeRownum) throws IOException {
        String fileName = file.getOriginalFilename();
        boolean isHigher = fileName.endsWith(".xlsx");
        Workbook workBook = isHigher ? new XSSFWorkbook(file.getInputStream()) :
                new HSSFWorkbook(file.getInputStream());
        Sheet sheet = workBook.getSheetAt(0);
        return toDataTable(sheet, includeRownum);
    }

    public static DataTable toDataTable(MultipartFile file) throws IOException {
        return toDataTable(file, false);
    }

    public static DataTable toDataTable(String filePath, boolean includeRownum) {
        Workbook workBook = getWorkbook(filePath);
        Sheet sheet = workBook.getSheetAt(0);
        return toDataTable(sheet, includeRownum);
    }

    public static DataTable toDataTable(String filePath) {
        Workbook workBook = getWorkbook(filePath);
        Sheet sheet = workBook.getSheetAt(0);
        return toDataTable(sheet);
    }

    /*public static DataTable toDataTable(File file, boolean includeRownum) throws IOException {
        String fileName = file.getName();
        boolean isHigher = fileName.endsWith(".xlsx");
        Workbook workBook = isHigher ? new XSSFWorkbook(file.getInputStream()) :
                new HSSFWorkbook(file.getInputStream());
        Sheet sheet = workBook.getSheetAt(0);
        return toDataTable(sheet, includeRownum);
    }

    public static DataTable toDataTable(File file) throws IOException {
        return toDataTable(file, false);
    }*/

    public static Workbook getWorkbook(String filePath) {
        Workbook workBook = null;
        boolean isHigher = filePath.endsWith(".xlsx");

        try (FileInputStream file = new FileInputStream(filePath)) {
            if (isHigher) {
                workBook = new XSSFWorkbook(file);
            } else {
                workBook = new HSSFWorkbook(file);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workBook;
    }

    public static Object getCellValue(Cell cell) {
        if(cell == null){
            return null;
        }
        switch (cell.getCellTypeEnum()) {
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case NUMERIC:
                // 是否是日期格式
                if(HSSFDateUtil.isCellDateFormatted(cell)){
                    Date d = (Date) cell.getDateCellValue();
                    DateFormat df2 = new SimpleDateFormat(d.toString().indexOf(":") > 0 ? "yyyy-MM-dd hh:mm:ss" : "yyyy-MM-dd");//HH:mm:ss
                    return df2.format(d);
                }
                /*if (DateUtil.isCellDateFormatted(cell)) {
                    // 读取日期格式
                    return cell.getDateCellValue();//DateUtil.(cell.getDateCellValue(), "yyyy-MM-dd");
                } */
                else {
                    return cell.getNumericCellValue();
                }
            case FORMULA:
                return cell.getCellFormula();
            case ERROR:
                return cell.getErrorCellValue();
            default:
                return cell.getStringCellValue();
        }
    }
}
